﻿CREATE PROCEDURE [dbo].[GetRentProperty]
@RentType NVARCHAR (3), @PriceMax BIGINT=0, @PriceMin BIGINT=0, @Project NVARCHAR (30)='', @FloorSize INT=0, @LandSize INT=0, @Location NVARCHAR (30)='', @offset INT=0, @next INT=20, @FloorSizeMin INT=0, @LandSizeMin INT=0
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
-- Insert statements for procedure here
IF @RentType='RR'
select a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email, f.SmallUrl as StaffPicURL,a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.FloorLoad,a.Ceiling,a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
''  as Condition,
 a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,
case 
when ISNULL(a.RRAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RRAmt,0) 
when ISNULL(a.RCAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RCAmt,0) 
when ISNULL(a.RIAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RIAmt,0) 
when ISNULL(a.RLAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RIAmt,0) then ISNULL(a.RLAmt,0) 
else 99999
end
as RentPrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.RentRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.rlisterid = f.staffid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_picture g on a.propertyid = g.propertyid
where a.rentview = 1 
--and b.statusdelete = 0 and c.statusdelete=0 
--and d.statusdelete = 0 and e.statusdelete = 0 
and f.statusdelete = 0
and (a.RRYes = 1)
and a.RRelistStatus = 0 and a.AgentFeedbackRent is null
and (a.RRAmt <= @PriceMax or @PriceMax = 0)
and (a.RRAmt >= @PriceMin  or @PriceMin = 0)
and (a.BUSF <= @FloorSize or @FloorSize = 0)
and (a.BUSF >= @FloorSizeMin or @FloorSizeMin = 0)
and (a.LASF <= @LandSize or @LandSize = 0)
and (a.LASF >= @LandSizeMin or @LandSizeMin = 0)
and (@Location = '' or  h.Project like '%' + @Location + '%' or c.Location like '%' + @Location + '%')
and (@Project = '' or b.Road like '%' + @Project + '%' or c.Location like '%' + @Project + '%' or d.State like '%' + @Project + '%' or h.Project like '%' + @Project + '%')
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl, a.LASF, a.BUSF,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,a.Condition, a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc,a.RentRemark
order by a.UpdateDate desc,a.CreateDate desc
--OFFSET @offset ROWS FETCH NEXT @next ROWS ONLY
ELSE IF @RentType = 'RL'
select a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.SmallUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
''  as Condition,
 a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,
case 
when ISNULL(a.RRAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RRAmt,0) 
when ISNULL(a.RCAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RCAmt,0) 
when ISNULL(a.RIAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RIAmt,0) 
when ISNULL(a.RLAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RIAmt,0) then ISNULL(a.RLAmt,0) 
else 99999
end
as RentPrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.RentRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.rlisterid = f.staffid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_picture g on a.propertyid = g.propertyid
where a.rentview = 1 
--and b.statusdelete = 0 and c.statusdelete=0 
--and d.statusdelete = 0 and e.statusdelete = 0 
and f.statusdelete = 0
and (a.RIYes=1)
and a.RRelistStatus = 0 and a.AgentFeedbackRent is null
and (a.RLAmt <= @PriceMax or @PriceMax = 0)
and (a.RLAmt >= @PriceMin  or @PriceMin = 0)
and (a.BUSF <= @FloorSize or @FloorSize = 0)
and (a.BUSF >= @FloorSizeMin or @FloorSizeMin = 0)
and (a.LASF <= @LandSize or @LandSize = 0)
and (a.LASF >= @LandSizeMin or @LandSizeMin = 0)
and (@Location = '' or  h.Project like '%' + @Location + '%' or c.Location like '%' + @Location + '%')
and (@Project = '' or b.Road like '%' + @Project + '%' or c.Location like '%' + @Project + '%' or d.State like '%' + @Project + '%' or h.Project like '%' + @Project + '%')
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl , a.LASF, a.BUSF,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition, a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc,a.RentRemark
order by a.UpdateDate desc,a.CreateDate desc
--OFFSET @offset ROWS FETCH NEXT @next ROWS ONLY
ELSE IF @RentType = 'RI'
select a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.SmallUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.FloorLoad,a.Ceiling,a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
''  as Condition,
 a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,
case 
when ISNULL(a.RRAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RRAmt,0) 
when ISNULL(a.RCAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RCAmt,0) 
when ISNULL(a.RIAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RIAmt,0) 
when ISNULL(a.RLAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RIAmt,0) then ISNULL(a.RLAmt,0) 
else 99999
end
as RentPrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.RentRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.rlisterid = f.staffid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_picture g on a.propertyid = g.propertyid
where a.rentview = 1 
--and b.statusdelete = 0 and c.statusdelete=0 
--and d.statusdelete = 0 and e.statusdelete = 0 
and f.statusdelete = 0
and (a.RIYes=1)
and a.RRelistStatus = 0 and a.AgentFeedbackRent is null
and (a.RIAmt <= @PriceMax or @PriceMax = 0)
and (a.RIAmt >= @PriceMin  or @PriceMin = 0)
and (a.BUSF <= @FloorSize or @FloorSize = 0)
and (a.BUSF >= @FloorSizeMin or @FloorSizeMin = 0)
and (a.LASF <= @LandSize or @LandSize = 0)
and (a.LASF >= @LandSizeMin or @LandSizeMin = 0)
and (@Location = '' or  h.Project like '%' + @Location + '%' or c.Location like '%' + @Location + '%')
and (@Project = '' or b.Road like '%' + @Project + '%' or c.Location like '%' + @Project + '%' or d.State like '%' + @Project + '%' or h.Project like '%' + @Project + '%')
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl, a.LASF, a.BUSF,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition, a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc,a.RentRemark
order by a.UpdateDate desc,a.CreateDate desc
--OFFSET @offset ROWS FETCH NEXT @next ROWS ONLY
ELSE IF @RentType = 'RC'
select a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.SmallUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
''  as Condition,
 a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,
case 
when ISNULL(a.RRAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RRAmt,0) 
when ISNULL(a.RCAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RCAmt,0) 
when ISNULL(a.RIAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RIAmt,0) 
when ISNULL(a.RLAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RIAmt,0) then ISNULL(a.RLAmt,0) 
else 99999
end
as RentPrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.RentRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.rlisterid = f.staffid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_picture g on a.propertyid = g.propertyid
where a.rentview = 1 
--and b.statusdelete = 0 and c.statusdelete=0 
--and d.statusdelete = 0 and e.statusdelete = 0 
and f.statusdelete = 0
and (a.RCYes=1)
and a.RRelistStatus = 0 and a.AgentFeedbackRent is null
and (a.RCAmt <= @PriceMax or @PriceMax = 0)
and (a.RCAmt >= @PriceMin  or @PriceMin = 0)
and (a.BUSF <= @FloorSize or @FloorSize = 0)
and (a.BUSF >= @FloorSizeMin or @FloorSizeMin = 0)
and (a.LASF <= @LandSize or @LandSize = 0)
and (a.LASF >= @LandSizeMin or @LandSizeMin = 0)
and (@Location = '' or  h.Project like '%' + @Location + '%' or c.Location like '%' + @Location + '%')
and (@Project = '' or b.Road like '%' + @Project + '%' or c.Location like '%' + @Project + '%' or d.State like '%' + @Project + '%' or h.Project like '%' + @Project + '%')
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl , a.LASF, a.BUSF,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition, a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc,a.RentRemark
order by a.UpdateDate desc,a.CreateDate desc
--OFFSET @offset ROWS FETCH NEXT @next ROWS ONLY
ELSE
select 'WRONG INPUT PARAMETER(RR/RL/RC/RI)' AS NORESULT
END

